#!/bin/bash
if [ -n "$1" ]
then
do_date=$1
else
do_date=`date -d "-1 day" +%F`
fi



sql="
with member_start as (
select * from dws.dws_member_start_day
where dt > date_add('$do_date',-7) and dt <= '$do_date'
),
tmp_gid as(
select
device_id, 
date_sub(dt , row_number() over (partition by device_id order by datediff(dt, date_add('$do_date',-7)))) gid
from member_start
)
insert overwrite table dws.dws_member_7d_cont_active
partition (dt='$do_date')
select device_id, count(*)
from tmp_gid
group by device_id, gid;
"

hive -e "$sql"